﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Entity;

namespace DAL
{
    public class UserDAL : SqlDataProvider
    {
        #region[User_GetById]
        public List<User> User_GetById(string Id)
        {
            List<Entity.User> list = new List<Entity.User>();
            using (SqlCommand dbCmd = new SqlCommand("sp_User_GetById", GetConnection()))
            {

                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Id", Id));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(User.UserIDataReader(dr));
                    }
                }
                dr.Close();

            }
            return list;
        }
        #endregion
        #region[User_GetByTop]
        public List<User> User_GetByTop(string Top, string Where, string Order)
        {
            List<Entity.User> list = new List<Entity.User>();
            using (SqlCommand dbCmd = new SqlCommand("sp_User_GetByTop", GetConnection()))
            {

                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Top", Top));
                dbCmd.Parameters.Add(new SqlParameter("@Where", Where));
                dbCmd.Parameters.Add(new SqlParameter("@Order", Order));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(User.UserIDataReader(dr));
                    }
                }
                dr.Close();

            }
            return list;
        }
        #endregion
        #region[User_GetByAll]
        public List<User> User_GetByAll()
        {
            List<Entity.User> list = new List<Entity.User>();
            using (SqlCommand dbCmd = new SqlCommand("sp_User_GetByAll", GetConnection()))
            {

                dbCmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(User.UserIDataReader(dr));
                    }
                }
                dr.Close();

            }
            return list;
        }
        #endregion
        #region[User_ChangePass]
        public bool User_ChangePass(string UserName, string NewPassword)
        {
            try
            {
                ExecuteNonQuery("Update [user] set [password]='" + NewPassword + "' where Username= '" + UserName + "'");
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion
        #region[User_Paging]
        public List<User> User_Paging(string CurentPage, string PageSize)
        {
            List<Entity.User> list = new List<Entity.User>();
            using (SqlCommand dbCmd = new SqlCommand("sp_User_Paging", GetConnection()))
            {

                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@CurentPage", CurentPage));
                dbCmd.Parameters.Add(new SqlParameter("@PageSize", PageSize));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(User.UserIDataReader(dr));
                    }
                }
                dr.Close();

            }
            return list;
        }
        #endregion
        #region[User_Insert]
        public bool User_Insert(User data)
        {
            using (SqlCommand dbCmd = new SqlCommand("sp_User_Insert", GetConnection()))
            {
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Name", data.Name));
                dbCmd.Parameters.Add(new SqlParameter("@Username", data.Username));
                dbCmd.Parameters.Add(new SqlParameter("@Password", data.Password));
                dbCmd.Parameters.Add(new SqlParameter("@Level", data.Level));
                dbCmd.Parameters.Add(new SqlParameter("@Admin", data.Admin));
                dbCmd.Parameters.Add(new SqlParameter("@Ord", data.Ord));
                dbCmd.Parameters.Add(new SqlParameter("@Active", data.Active));
                dbCmd.ExecuteNonQuery();
            }
            //Clear cache
            System.Web.HttpContext.Current.Cache.Remove("User");
            return true;
        }
        #endregion
        #region[User_Update]
        public bool User_Update(User data)
        {
            using (SqlCommand dbCmd = new SqlCommand("sp_User_Update", GetConnection()))
            {
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Id", data.Id));
                dbCmd.Parameters.Add(new SqlParameter("@Name", data.Name));
                dbCmd.Parameters.Add(new SqlParameter("@Username", data.Username));
                dbCmd.Parameters.Add(new SqlParameter("@Password", data.Password));
                dbCmd.Parameters.Add(new SqlParameter("@Level", data.Level));
                dbCmd.Parameters.Add(new SqlParameter("@Admin", data.Admin));
                dbCmd.Parameters.Add(new SqlParameter("@Ord", data.Ord));
                dbCmd.Parameters.Add(new SqlParameter("@Active", data.Active));
                dbCmd.ExecuteNonQuery();
            }
            //Clear cache
            System.Web.HttpContext.Current.Cache.Remove("User");
            return true;
        }
        #endregion
        #region[User_Delete]
        public bool User_Delete(string Id)
        {
            using (SqlCommand dbCmd = new SqlCommand("sp_User_Delete", GetConnection()))
            {
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Id", Id));
                dbCmd.ExecuteNonQuery();
            }
            //Clear cache
            System.Web.HttpContext.Current.Cache.Remove("User");
            return true;
        }
        #endregion
        #region[User_Check_Login]
        public bool User_Check_Login(String Username, string Pass)
        {
            using (SqlCommand dbCmd = new SqlCommand("Check_Login", GetConnection()))
            {
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Username", Username));
                dbCmd.Parameters.Add(new SqlParameter("@Pass", Pass));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows) return true;
                dr.Close();
            }
            return false;
        }
        #endregion
    }
}
